from openpyxl import Workbook
from openpyxl import load_workbook
import pathlib
import os
import sqlite3
import configparser
import sys
import easygui
from inc import *


db=sqlite3.connect(str(root/"yj.db"))
cur = db.cursor()


try:
    lock=conf.get("Lock","lock")
except:
    lock=False
finally:
    if(lock=="true"):
        a=input("数据已被锁定")
        sys.exit(0)

# priority=conf.get("config","priority")
# firstList=priority.split(",")
# priority=None
firstList=conf.get("config","priority").split(",")
cellsize=conf.getint("config","cellsize")
#firstList=['7_75','9_63','8_69'] #优先排列(年级_班级)

print('优先排列(年级_班级)',firstList)

file=root/pathlib.Path("班级座位.xlsx")
#print (file)
wb = load_workbook(str(file))
wb_active=wb.active
wb_row=wb_active.rows
room={}
for i in range(2,wb_active.max_column+1):   
    #print(wb_active.cell(row=1,column=i).value)
    name=str(getClassNo(str(wb_active.cell(row=1,column=i).value)))
    array=[]
    for n in range(2,wb_active.max_row+1):
        if(wb_active.cell(row=n,column=i).value!=None):
            array.append(wb_active.cell(row=n,column=i).value)
            array.append(wb_active.cell(row=n,column=i).value)
    room[name]=(len(array),array)
print(room)
sql="select bj from main group by bj"
r=cur.execute(sql)
slist={}
for i in r.fetchall():
    slist[i[0]]=[]
    sql="select id from main where bj ='%s'"%(str(i[0]))
    r1=cur.execute(sql)
    for n in r1.fetchall():
        slist[i[0]].append(n[0])
for k in slist:
    print(k,len(slist[k]))
gradeCount={} #年级人数
gradelist={} #年级列表

njl=cur.execute("select nj from main group by nj")
njl=njl.fetchall()
for i in njl:
    r=cur.execute("select count(*) from main where nj=%s"%(i[0]))
    r=r.fetchone()
    gradeCount[str(i[0])]=r[0]
print(gradeCount)
for i in njl:
    r=cur.execute("select bj from main where nj=%s group by bj"%(i[0]))
    r=r.fetchall()
    list={}
    for n in r:
        print(n[0])
        list[str(n[0])]=len(slist[n[0]])
    gradelist[str(i[0])]=list
print(gradelist)

print(gradeCount)

def sortList(grade):
    global gradelist
    grade=str(grade)
    r=sorted(gradelist[grade].items(),key=lambda x: x[1],reverse=True)#降序排列，拉开班级间距离
    d=-1
    for i in range(0,len(r)):
        # print(r[-(i-d)][0])
        if(r[-(i-d)][0] not in firstList):
            r.pop(-(i-d))
            d=d+1
            if(len(r)<=cellsize):
                break

    return r

zadd=conf.getint("config","grademix")

zsub = 0
for k in room:
    #print(k,room[k][0])
    ignoreRoom=conf.items("IgnoreRoom")
    gradeSort=sorted(gradeCount.items(),key=lambda x: x[1],reverse=True)
    for (i,v) in ignoreRoom:
        igl=v.split(",")
        if k in igl :
            for m,n in gradeSort:
                if m==i:
                    gradeSort.remove((m,n))
    while(len(gradeSort)>conf.getint("config","grademix")):
        gradeSort.pop(-1)
    #print(gradeSort)
    zt=0
    
    for g in gradeSort:
        zt=zt+1
        z=zt
        p=1
        fl=True
        while fl:
            bs=sortList(g[0])
            for info in bs:
                if(gradeCount[str(g[0])]<=0):
                    if zadd>3 : #排完一个年级，组距减1
                        zsub = 1
                    fl=False
                    break
                if(len(slist[info[0]])!=0):
                    id=slist[info[0]][0]
                    slist[info[0]].pop(0)
                    gradeCount[str(g[0])]=gradeCount[str(g[0])]-1
                    gradelist[g[0]][info[0]]=gradelist[g[0]][info[0]]-1
                    cur.execute("UPDATE main SET 'kc' = '{kc}', 'z' = '{z}', 'p' = '{p}' WHERE ID = {id}".format(kc=k,z=z,p=p,id=id))
                    #db.commit()
                    #print(info[0],id,k,p,z)
                    #print(gradeCount)
                    p=p+1
                if(p > room[k][1][z-1]):
                    z=z+zadd
                    p=1
                    if(z > room[k][0]):
                        fl=False
                        break
        print("{0}考场{1}年级已完成,剩余{2}人".format(k,g[0],gradeCount[str(g[0])]))
    zadd = zadd-zsub #调整组距
    db.commit()
#print(gradelist)
#print(gradeCount)
sy=0
stt=""
for k in gradeCount :
    print("{0}年级剩余{1}人".format(k,gradeCount[k]))
    sy=sy+gradeCount[k]
if(sy==0):
    stt="已排完"
else:
    for i in gradelist:
        for n in gradelist[i]:
            if(gradelist[i][n]!=0):
                print("{0}年级{1}班剩余{2}人".format(i,n,gradelist[i][n]))
    stt="考场不够，还有{0}考生未安排考场！".format(sy) 
try:
    lock=conf.get("Lock","lock")
    
except:
    lock=False
finally:
    if(not lock=="ignore"):
        conf.set("Lock","# true false ignore")
        conf.set("Lock","lock","true")
conf.set("alias","#班级号 = 班级名")
conf.write(open(str(root/"config.ini"), "w",encoding="utf-8"))
easygui.msgbox(stt)
